Connection Object, Connections Collection Example

This example demonstrates the Connection object and Connections collection by opening a Microsoft Jet Database object and two ODBCDirect Connection objects and listing the properties available to each object.

Sub ConnectionObjectX()

   Dim wrkJet as Workspace
   Dim dbsNorthwind As Database
   Dim wrkODBC As Workspace
   Dim conPubs As Connection
   Dim conPubs2 As Connection
   Dim conLoop As Connection
   Dim prpLoop As Property

   ' Open Microsoft Jet Database object.
   Set wrkJet = CreateWorkspace("NewJetWorkspace", _
      "admin", "", dbUseJet)
   Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb")

   ' Create ODBCDirect Workspace object and open Connection
   ' objects.
   Set wrkODBC = CreateWorkspace("NewODBCWorkspace", _
      "admin", "", dbUseODBC)
      
   ' Note: The DSNs referenced below must be configured to 
   '       use Microsoft Windows NT Authentication Mode to 
   '       authorize user access to the Microsoft SQL Server.
   Set conPubs = wrkODBC.OpenConnection("Connection1", , , _
      "ODBC;DATABASE=pubs;DSN=Publishers")
      
   Set conPubs2 = wrkODBC.OpenConnection("Connection2", , _
      True, "ODBC;DATABASE=pubs;DSN=Publishers")

   Debug.Print "Database properties:"

   With dbsNorthwind
      ' Enumerate Properties collection of Database object.
      For Each prpLoop In .Properties
         On Error Resume Next
         Debug.Print "  " & prpLoop.Name & " = " & _
            prpLoop.Value
         On Error GoTo 0
      Next prpLoop
   End With

   ' Enumerate the Connections collection.
   For Each conLoop In wrkODBC.Connections
      Debug.Print "Connection properties for " & _
         conLoop.Name & ":"

      With conLoop
         ' Print property values by explicitly calling each
         ' Property object; the Connection object does not
         ' support a Properties collection.
         Debug.Print "  Connect = " & .Connect
         ' Property actually returns a Database object.
         Debug.Print "  Database[.Name] = " & _
            .Database.Name
         Debug.Print "  Name = " & .Name
         Debug.Print "  QueryTimeout = " & .QueryTimeout
         Debug.Print "  RecordsAffected = " & _
            .RecordsAffected
         Debug.Print "  StillExecuting = " & _
            .StillExecuting
         Debug.Print "  Transactions = " & .Transactions
         Debug.Print "  Updatable = " & .Updatable
      End With

   Next conLoop

   dbsNorthwind.Close
   conPubs.Close
   conPubs2.Close
   wrkJet.Close
   wrkODBC.Close

End Sub